package dblayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import modellayer.Product;
import modellayer.Supplier;


public class ProductDB implements IFProductDB {
	private Connection con;
	private boolean successCheck;
	
	public ProductDB() {
		con = DBConnection.getInstance().getDBcon();
	}
	
	@Override
	public int insertProduct(Product productObj) throws Exception {
		int rc = -1;
		
		String query = "INSERT INTO Product (barcode, name, description, purchagePrice, salesPrice, rentPricePrDay, countryOfOrigin, stock, minStock, supplierId) VALUES ('"+productObj.getBarcode()+"', '"+productObj.getName()+"', '"+productObj.getDescription()+"', '"+productObj.getPurchagePrice()+"', '"+productObj.getSalesPrice()+"', '"+productObj.getRentPricePrDay()+"', '"+productObj.getCountryOfOrigin()+"', '"+productObj.getStock()+"', '"+productObj.getMinStock()+"', '"+productObj.getSupplierObj().getSupplierId()+"');";
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch(SQLException ex) {
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
			
			throw new Exception("Failed to insert Product to Database!");
		}
		
		return rc;
	}
	
	@Override
	public Product findProductByBarcode(String barcode) {
		String wClause = " barcode='"+barcode+"'";
		return singleWhere(wClause);
	}

	@Override
	public Product findProductByName(String name) {
		String wClause = " name='"+name+"'";
		return singleWhere(wClause);
	}
	
	@Override
	public boolean deleteProductByProductNo(int productNo)
	{
		successCheck = false;
		
		String query = "DELETE FROM Product WHERE productNo='"+productNo+"'";
		
		try 
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeQuery(query);			
			stmt.close();
			successCheck = true;
		} 
		catch (Exception ex) 
		{
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
		}
		
		return successCheck;
	}
	
	@Override
	public boolean deleteProductByBarcode(String barcode)
	{
		successCheck = false;
		
		String query = "DELETE FROM Product WHERE barcode='"+barcode+"'";
		
		try 
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeQuery(query);			
			stmt.close();
			successCheck = true;
		} 
		catch (Exception ex) 
		{
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
		}
		
		return successCheck;
	}
	
	private Product singleWhere(String wClause) {
		ResultSet results;
		Product productObj = null;
		
		String query = buildQuery(wClause);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if (results.next()) {
				productObj = buildProduct(results);
				//stmt.close(); ??
			} else {
				productObj = null;
			}
			
			stmt.close();
		} catch (Exception ex) {
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
		}
		
		return productObj;
	}

	private ArrayList<Product> miscWhere(String wClause)
	{
            ResultSet results;
	    ArrayList<Product> list = new ArrayList<Product>();	
		
	    String query =  buildQuery(wClause);
  
            try{ 
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
			Product productObj = buildProduct(results);	
                 list.add(productObj);
         		
		}
                 stmt.close();     
                 
		}
	 	catch(Exception e){
	 		
			e.printStackTrace();
	 	}
		return list;
	}
	
	private String buildQuery(String wClause) {
		String query = "SELECT productNo, barcode, name, description, purchagePrice, salesPrice, rentPricePrDay, countryOfOrigin, stock, minStock, supplierId FROM Product";
		
		if (!wClause.isEmpty()) {
			query += " WHERE " + wClause;
		}
		
		return query;
	}
	
	private Product buildProduct(ResultSet result) {
		Product productObj = null;
		try {
			productObj = new Product(result.getInt("productNo"), result.getString("barcode"), result.getString("name"), result.getString("description"), result.getDouble("purchagePrice"), result.getDouble("salesPrice"), result.getDouble("rentPricePrDay"), result.getString("countryOfOrigin"), result.getInt("stock"), result.getInt("minStock"), null, new Supplier(result.getInt("supplierId")));
		} catch (SQLException e) {
			System.out.println("Error building Product object!\nError: " + e);
		}
		
		
		return productObj;
	}

}
